package br.com.iqt;

import br.com.iqt.zql.ZFromItem;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.StringTokenizer;
import java.util.Vector;

/**
 *
 * @author Arlino
 */
public class DriverStatisticsSqlserver implements DriverStatistics {
    private Dbms dbms;
    private String url;

    DriverStatisticsSqlserver(Dbms dbms) throws ClassNotFoundException {
        this.dbms = dbms;
        Class.forName(Dbms.driverPostgresql);
        this.url = "jdbc:sqlserver://" + dbms.getServer() + ";database=" + dbms.getDatabase();
    }

    @Override
    public boolean haveIndex(Vector tables, String column) throws SQLException {
        Connection connection = (Connection) DriverManager.getConnection(url, dbms.getUser(), dbms.getPassword());
        Statement stm = connection.createStatement();
        ResultSet rs;
        boolean result = false;
        for (Object object : tables) {
            ZFromItem tb = (ZFromItem) object;
            String table = tb.getTable();
            //String scheme = tb.getSchema();

            //if (scheme == null || scheme.isEmpty()) {
             //   scheme = "dbo";
            //}
           String sql = "select * from sys.index_columns where object_id = (select object_id from sys.objects "
                        + "where name = '" + table + "') and column_id = (select column_id from sys.columns "
                        + "where name = '" + column + "'and object_id = (select object_id from sys.objects "
                        + "where name = '" + table + "'))";
            rs = stm.executeQuery(sql);
            result = rs.next();

            if (result) {
                break;
            }
        }
    
        stm.close();
        connection.close();
        return result;
    }

    @Override
    public boolean isPrimaryKey(Vector tables, String column) throws SQLException {
        String user = this.dbms.getUser();
        String password = this.dbms.getPassword();
        Connection connection = (Connection) DriverManager.getConnection(this.url, user, password);
        
        Statement stm = connection.createStatement();;
        ResultSet rs = null;
        boolean result = false;
        for (Object object : tables) {
            ZFromItem fi = (ZFromItem) object;
            String table = fi.getTable();
            //String scheme = fi.getSchema();
            //if(scheme == null || scheme.equals(""))
            //    scheme = "dbo";

            String sql = "select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '"
                    + table + "' and COLUMN_NAME = '" + column + "' and CONSTRAINT_NAME = (select name "
                    + "from sys.key_constraints where parent_object_id = (select object_id from "
                    + "sys.tables where name = '" + table + "'))";
            rs = stm.executeQuery(sql);
            result = rs.next();

            if (result) 
                break;
        }
        stm.close();
        connection.close();
        return result;
    }

    @Override
    public ZFromItem getTableWithPrimaryKey(Vector tables, String column) throws SQLException {
        String user = this.dbms.getUser();
        String password = this.dbms.getPassword();
        Connection connection = (Connection) DriverManager.getConnection(this.url, user, password);

        Statement stm = connection.createStatement();
        ResultSet rs = null;
        ZFromItem tableWithPrimaryKey = null;
        for (Object object : tables) {
            ZFromItem tb = (ZFromItem) object;
            String table = tb.getTable();
            //String scheme = tb.getSchema();
            //if(scheme == null || scheme.equals(""))
            //    scheme = "public";

           String sql = "select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '"
                    + table + "' and COLUMN_NAME = '" + column + "' and CONSTRAINT_NAME = (select name "
                    + "from sys.key_constraints where parent_object_id = (select object_id from "
                    + "sys.tables where name = '" + table + "'))";
            rs = stm.executeQuery(sql);

            if (rs.next()) {
                tableWithPrimaryKey = tb;
                break;
            }
        }
        stm.close();
        connection.close();
        return tableWithPrimaryKey;
    }

    @Override
    public boolean isForingkeyFromTable1InTabel2(ZFromItem table, String column, Vector from) throws SQLException {
        Connection connection = (Connection) DriverManager.getConnection(this.url, dbms.getUser(), dbms.getPassword());
        Statement stm = connection.createStatement();

        boolean result = false;
        for (Object obj : from) {
            ZFromItem tb = (ZFromItem) obj;

            /*String schema1 = table.getSchema();
            if (schema1 == null || schema1.equals("")) {
                schema1 = "public";
            }
            

            String schema2 = tb.getSchema();
            if (schema2 == null || schema2.equals("")) {
                schema2 = "public";
            }*/
            String table1 = table.getTable();
            String table2 = tb.getTable();

            if (!table1.equalsIgnoreCase(table2) /*|| schema1.equalsIgnoreCase(schema2)*/) {
                String sql = "select * from sys.foreign_key_columns where parent_object_id = (select object_id "
                        + "from sys.objects where name = '" + table2 + "') and referenced_object_id = "
                        + "(select object_id from sys.objects where name = '" + table1 + "') and "
                        + "referenced_column_id =(select column_id from sys.columns where name = '" 
                        + column + "' and object_id = (select object_id from sys.objects where name = '" + table1+"'))";
                ResultSet rs = stm.executeQuery(sql);
                
                result = rs.next();
                if (result) {
                    break;
                }
            }
        }
        stm.close();
        connection.close();
        return result;
    }

    @Override
    public ZFromItem getTableWithForingkeyFromTable1InTabel2(ZFromItem table, String column, Vector from) throws SQLException {
        Connection connection = (Connection) DriverManager.getConnection(this.url, dbms.getUser(), dbms.getPassword());
        Statement stm = connection.createStatement();
        
        ZFromItem tableWithForingkey = null;
        for (Object obj : from) {
            ZFromItem tb = (ZFromItem) obj;

            /*tring schema1 = table.getSchema();
            if (schema1 == null) {
                schema1 = "";
            }

            String schema2 = tb.getSchema();
            if (schema2 == null) {
                schema1 = "";
            }*/
            String table1 = table.getTable();
            String table2 = tb.getTable();

            if (!table1.equalsIgnoreCase(table2) /*|| schema1.equalsIgnoreCase(schema2)*/) {
                String sql = "select * from sys.foreign_key_columns where parent_object_id = (select object_id "
                        + "from sys.objects where name = '" + table2 + "') and referenced_object_id = "
                        + "(select object_id from sys.objects where name = '" + table1 + "') and "
                        + "referenced_column_id =(select column_id from sys.columns where name = '" 
                        + column + "')";
                ResultSet rs = stm.executeQuery(sql);

                if (rs.next()) {
                    tableWithForingkey = tb;
                }
            }
        }
        stm.close();
        connection.close();
        return tableWithForingkey;
    }

    @Override
    public ArrayList getExecutionPlan(String sql) throws SQLException {
        Connection connection = (Connection) DriverManager.getConnection(this.url, dbms.getUser(), dbms.getPassword());
        Statement statement = connection.createStatement();
        
        statement.execute("SET SHOWPLAN_ALL ON");
        statement.execute("SET SHOWPLAN_TEXT ON");
        
        ResultSet resultSet = statement.executeQuery(sql);
        ArrayList list = new ArrayList();
        while (resultSet.next()) {
            list.add(resultSet.getString(1));
        }
        
        statement.execute("SET SHOWPLAN_TEXT OFF");
        statement.execute("SET SHOWPLAN_ALL OFF");
        statement.close();
        connection.close();
        return list;
    }

    @Override
    public String getRealRuntime(String sql) throws SQLException {
        Connection connection = (Connection) DriverManager.getConnection(this.url, dbms.getUser(), dbms.getPassword());
        Statement stm = connection.createStatement();

        long finalTime = 0;
        StringTokenizer stringTokenizer = new StringTokenizer(sql, ";");
        while (stringTokenizer.hasMoreElements()) {
            String sqlAux = stringTokenizer.nextToken();
            String executionType;
            if (!sqlAux.toUpperCase().trim().startsWith("SELECT") || sqlAux.trim().toUpperCase().contains(" INTO ")) {
                executionType = "executeUpdate";
            } else {
                executionType = "executeQuery";
            }

            long startTime = System.currentTimeMillis();
            if (executionType.equals("executeUpdate")) {
                stm.executeUpdate(sqlAux);
            } else {
                stm.executeQuery(sqlAux);
            }
            long endTime = System.currentTimeMillis();

            finalTime += endTime - startTime;
        }
        stm.close();
        connection.close();
        return (Long.toString(finalTime) + " ms");
    }
}
